package dataAccess;

import au.com.bytecode.opencsv.CSVWriter;
import businessEntities.ColumnsInformationContainer;
import businessEntities.MainLogger;
import businessEntities.exceptions.ColumnsQueryException;
import businessEntities.exceptions.ConnectionException;
import businessEntities.exceptions.QueryException;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Result;
import javax.xml.transform.Source;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import org.w3c.dom.Document;
import org.w3c.dom.Element;

public class DALFileExporter {

    /**
     * Export database table to a given file - CSV format.
     * @return true if and only if no exception occurred during the process.
     * @throws ConnectionException if the connection to the database is not reachable.
     * @throws QueryException for any problem during the executing of the query on the database.
     */
    public boolean exportWholeTableToCSV(File outFile, String databaseName, String tableName) {
        String query = "SELECT * FROM " + databaseName + "." + tableName;
        return exportResultsetToCSV(outFile, query);
    }

    /**
     * Export database table filtered under the conditions of <code>where</code> to a given file - CSV format.
     * @return true if and only if no exception occurred during the process.
     * @throws ConnectionException if the connection to the database is not reachable.
     * @throws QueryException for any problem during the executing of the query on the database.
     */
    public boolean exportFilteredTableToCSV(File outFile, String databaseName, String tableName, String where) {
        String query = "SELECT * FROM " + databaseName + "." + tableName + " WHERE " + where;
        return exportResultsetToCSV(outFile, query);
    }

    /**
     * Export result set to a given file - CSV format.
     * @return true if and only if no exception occurred during the process.
     * @throws ConnectionException if the connection to the database is not reachable.
     * @throws ColumnsQueryException for any exception during the process.
     * @throws QueryException for any problem during the executing of the query on the database.
     */
    public boolean exportResultsetToCSV(File outFile, String query) {

        Connection connection = SingeltonConnection.getConnection();
        boolean result = true;
        MainLogger.logger.info("Executing Query : [" + query + "]");
        try (Statement stm = connection.createStatement()) {
            MainLogger.logger.info("Start Export the ResultSet of Query : [" + query + "] to XML File.");
            try (CSVWriter writer = new CSVWriter(new FileWriter(outFile), ',')) {

                ResultSet rs = stm.executeQuery(query);
                writer.writeAll(rs, true);

            } catch (IOException ioEx) {
                MainLogger.logger.error("Export the ResultSet of Query : [" + query + "] to CSV File - FAILED.", ioEx);
                result = false;
            }
        } catch (SQLException sqlEx) {
            MainLogger.logger.error("Query : [" + query + "] - FAILED.", sqlEx);
            throw new QueryException("Query: '" + query + "' FAILED", sqlEx);
        }
        return result;
    }

    // XML
    /**
     * Export database table to a given file -  XML format.
     * @return true if and only if no exception occurred during the process.
     * @throws ConnectionException if the connection to the database is not reachable.
     * @throws ColumnsQueryException for any exception during the process
     * @throws QueryException for any problem during the executing of the query on the database.
     */
    public boolean exportWholeTableToXML(File outFile, String databaseName, String tableName) {
        String query = "SELECT * FROM " + databaseName + "." + tableName;
        DALInformationQuery infomationQuery = new DALInformationQuery();
        ColumnsInformationContainer infoContainer = infomationQuery.getColumnsInformation(databaseName, tableName);
        return exportResultsetToXML(outFile, query, infoContainer);
    }

    /**
     * Export database table filtered under the condition of <code>where</code> to a given file - XML format.
     * @return true if and only if no exception occurred during the process.
     * @throws ConnectionException if the connection to the database is not reachable.
     * @throws QueryException for any problem during the executing of the query on the database.
     */
    public boolean exportFilteredTableToXML(File outFile, String databaseName, String tableName, String where) {
        String query = "SELECT * FROM " + databaseName + "." + tableName + " WHERE " + where;
        DALInformationQuery infomationQuery = new DALInformationQuery();
        ColumnsInformationContainer infoContainer = infomationQuery.getColumnsInformation(databaseName, tableName);
        return exportResultsetToXML(outFile, query, infoContainer);
    }

    /**
     * @return true if and only if no exception occurred during the process.
     * @throws ConnectionException if the connection to the database is not reachable.
     * @throws QueryException for any problem during the executing of the query on the database.
     */
    private boolean exportResultsetToXML(File outFile, String query, ColumnsInformationContainer infoContainer) {
        Connection connection = SingeltonConnection.getConnection();

        MainLogger.logger.info("Executing Query : [" + query + "]");
        try (Statement stm = connection.createStatement()) {
            ResultSet rs = stm.executeQuery(query);
            ResultSetMetaData metaData = rs.getMetaData();
            int colCount = metaData.getColumnCount();

            MainLogger.logger.info("Start Export the ResultSet of Query : [" + query + "] to XML File.");
            DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
            DocumentBuilder builder = factory.newDocumentBuilder();
            Document doc = builder.newDocument();

            //Query Results
            Element results = doc.createElement("Results");
            doc.appendChild(results);

            //Information
            Element information = doc.createElement("Information");
            results.appendChild(information);

            Element host = doc.createElement("Host_Name");
            host.appendChild(doc.createTextNode(SingeltonConnection._host));
            information.appendChild(host);

            Element date = doc.createElement("Date");
            date.appendChild(doc.createTextNode(new Date().toString()));
            information.appendChild(date);

            Element querySentence = doc.createElement("Query");
            querySentence.appendChild(doc.createTextNode(query));
            information.appendChild(querySentence);

            //Meta Data
            Element meta = doc.createElement("Meta_Data");
            results.appendChild(meta);

            DALInformationQuery infomationQuery = new DALInformationQuery();


            //Create 'Columns' element
            Element columns = doc.createElement("Columns");
            meta.appendChild(columns);
            for (int i = 1; i <= colCount; i++) {
                Element column = doc.createElement("Column");
                column.setAttribute("ID", "" + i);
                columns.appendChild(column);

                String columnName = metaData.getColumnName(i);
                Element name = doc.createElement("Name");
                name.appendChild(doc.createTextNode(columnName));
                column.appendChild(name);

                String columnType = infoContainer.getColumnType(columnName);
                Element type = doc.createElement("Type");
                type.appendChild(doc.createTextNode(columnType));
                column.appendChild(type);

                Boolean n = infoContainer.isNullableColumn(columnName);
                Element nullable = doc.createElement("Nullable");
                nullable.appendChild(doc.createTextNode(n.toString()));
                column.appendChild(nullable);
            }

            //Data
            Element data = doc.createElement("Data");
            results.appendChild(data);
            Element rows = doc.createElement("Rows");
            data.appendChild(rows);
            int rowCount = 0;
            while (rs.next()) {
                Element row = doc.createElement("Row");
                row.setAttribute("ID", "" + (++rowCount));
                rows.appendChild(row);
                for (int i = 1; i <= colCount; i++) {
                    String columnName = metaData.getColumnName(i);
                    Object value = rs.getObject(i);
                    Element node = doc.createElement(columnName);
                    if (value != null) {
                        node.appendChild(doc.createTextNode(value.toString()));
                        row.appendChild(node);
                    } else {
                        Element nullNode = doc.createElement("Null");
                        node.appendChild(nullNode);
                        row.appendChild(node);
                    }
                }
            }

            Element columnsCount = doc.createElement("Columns_Count");
            columnsCount.appendChild(doc.createTextNode("" + metaData.getColumnCount()));
            information.appendChild(columnsCount);

            Element rowsCount = doc.createElement("Rows_Count");
            rowsCount.appendChild(doc.createTextNode("" + rowCount));
            information.appendChild(rowsCount);

            Source source = new DOMSource(doc);
            Result result = new StreamResult(outFile);
            Transformer xformer = TransformerFactory.newInstance().newTransformer();
            xformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "no");
            xformer.setOutputProperty(OutputKeys.METHOD, "xml");
            xformer.setOutputProperty(OutputKeys.ENCODING, "ISO-8859-1");
            xformer.transform(source, result);

        } catch (TransformerException | ParserConfigurationException | ColumnsQueryException ex) {
            MainLogger.logger.error("Export the ResultSet of Query : [" + query + "] to XML File - FAILED.", ex);
            return false;
        } catch (SQLException sqlEx) {
            MainLogger.logger.error("Query : [" + query + "] - FAILED.", sqlEx);
            throw new QueryException("Query: '" + query + "' FAILED", sqlEx);
        }

        return true;
    }
    
    /**
     * Save Document to file.
     * @return true if and only if export succeeded.
     */
    public boolean saveTreeToFile(Document doc, File outFile){
        boolean answer = true;
        try {
               Source source = new DOMSource(doc);
               Result result = new StreamResult(outFile);
               Transformer xformer = TransformerFactory.newInstance().newTransformer();
               xformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "no");
               xformer.setOutputProperty(OutputKeys.METHOD, "xml");
               xformer.setOutputProperty(OutputKeys.ENCODING, "ISO-8859-1");
               xformer.transform(source, result);
          
        } catch (TransformerException | NullPointerException ex) {
            MainLogger.logger.error("Could not export tree structure to file. Path : " + outFile, ex);
            answer = false;
        }
        
        return answer;
    }
}
